package android.got.tasks;

import java.util.List;

import com.google.api.client.util.DateTime;
import com.google.api.services.tasks.model.Task;
import com.google.api.services.tasks.model.TaskList;
import com.google.api.services.tasks.model.TaskLists;
import com.google.api.services.tasks.model.Tasks;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class TasksDBHelper extends SQLiteOpenHelper {

    private static final int    DATABASE_VERSION    = 2;
    private static final String TASKS_DB_NAME       = "GotTasksDB";

    private static final String TASK_TABLE_NAME     = "Task";
    private static final String TASKLIST_TABLE_NAME = "TaskList";

    public static final String TASK_CLM_PK         = "_id";
    public static final String TASK_CLM_ID         = "id";
    public static final String TASK_CLM_TITLE      = "title";
    public static final String TASK_CLM_UPDATED    = "updated";
    public static final String TASK_CLM_SELFLINK   = "selfLink";
    public static final String TASK_CLM_PARENT     = "parent";
    public static final String TASK_CLM_POSITION   = "position";
    public static final String TASK_CLM_NOTES      = "notes";
    public static final String TASK_CLM_STATUS     = "status";
    public static final String TASK_CLM_DUE        = "due";
    public static final String TASK_CLM_COMPLETED  = "completed";
    public static final String TASK_CLM_DELETED    = "deleted";
    public static final String TASK_CLM_HIDDEN     = "hidden";
    public static final String TASK_CLM_MODIFIED   = "modified";
    public static final String TASK_CLM_TASKLISTID = "taskListID";

    public static final String TASK_COMPL          = TASK_CLM_COMPLETED;
    public static final String TASK_ACTION         = "needsAction";

    public static final int FALSE = 0;
    public static final int TRUE  = 1;

    public TasksDBHelper(Context context) {
        super(context, TASKS_DB_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // create table for our tasks#taskList resource
        db.execSQL("CREATE TABLE " + TASKLIST_TABLE_NAME + " ("
                + TASK_CLM_PK + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + TASK_CLM_ID + " TEXT,"
                + TASK_CLM_TITLE + " TEXT,"
                + TASK_CLM_SELFLINK + " TEXT"
                + ");");

        // create table for our tasks#task resource
        db.execSQL("CREATE TABLE " + TASK_TABLE_NAME + " ("
                + TASK_CLM_PK + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + TASK_CLM_ID + " TEXT,"
                + TASK_CLM_TITLE + " TEXT," // mutable
                + TASK_CLM_UPDATED + " TEXT,"
                + TASK_CLM_SELFLINK + " TEXT,"
                + TASK_CLM_PARENT + " TEXT,"
                + TASK_CLM_POSITION + " TEXT,"
                + TASK_CLM_NOTES + " TEXT," // mutable
                + TASK_CLM_STATUS + " TEXT," // mutable
                + TASK_CLM_DUE + " TEXT," // mutable
                + TASK_CLM_COMPLETED + " TEXT," // mutable
                + TASK_CLM_DELETED + " INTEGER," // is boolean 0 false, 1 true
                + TASK_CLM_HIDDEN + " INTEGER,"  // is boolean 0 false, 1 true
                + TASK_CLM_MODIFIED + " INTEGER," // is boolean 0 false, 1 true
                + TASK_CLM_TASKLISTID + " INTEGER,"
                + "FOREIGN KEY(" + TASK_CLM_TASKLISTID + ") REFERENCES " + TASKLIST_TABLE_NAME + "(" + TASK_CLM_ID + ")"
                + ");");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Kills the table and existing data
        db.execSQL("DROP TABLE IF EXISTS " + TASK_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + TASKLIST_TABLE_NAME);

        // Recreates the database with a new version
        onCreate(db);
    }

    private ContentValues createContentTaskList(TaskList taskList, final boolean modified) {
        ContentValues values = new ContentValues();

        values.put(TASK_CLM_ID, taskList.getId());
        values.put(TASK_CLM_TITLE, taskList.getTitle());
        values.put(TASK_CLM_SELFLINK, taskList.getSelfLink());
        return values;
    }

    /* create db friendly format for the information*/
    private ContentValues createContentTasks(Task task, final int taskListID, final boolean modified) {
        ContentValues values = new ContentValues();

        values.put(TASK_CLM_ID, task.getId());
        values.put(TASK_CLM_TITLE, task.getTitle());

        if (task.getUpdated() != null)
            values.put(TASK_CLM_UPDATED, task.getUpdated().toString());

        values.put(TASK_CLM_SELFLINK, task.getSelfLink());
        values.put(TASK_CLM_PARENT, task.getParent());
        values.put(TASK_CLM_POSITION, task.getPosition());
        values.put(TASK_CLM_NOTES, task.getNotes());
        values.put(TASK_CLM_STATUS, task.getStatus());

        if (task.getDue() != null)
            values.put(TASK_CLM_DUE, task.getDue().toString());

        if (task.getCompleted() != null)
            values.put(TASK_CLM_COMPLETED, task.getCompleted().toString());

        if (task.getHidden() != null)
            values.put(TASK_CLM_HIDDEN, task.getHidden());
        else
            values.put(TASK_CLM_HIDDEN, FALSE);

        if (task.getDeleted() != null)
            values.put(TASK_CLM_DELETED, task.getDeleted());
        else
            values.put(TASK_CLM_DELETED, FALSE);

        if (modified)
            values.put(TASK_CLM_MODIFIED, TRUE);
        else
            values.put(TASK_CLM_MODIFIED, FALSE);

        values.put(TASK_CLM_TASKLISTID, taskListID);

        return values;
    }

    public void updateTasksDB(final Tasks tasks, final int taskListID) {
        for (Task task : tasks.getItems()) {
            int res = isTaskNewer(task);
            if(res == -1) {
                addTask(task, taskListID);
            } else if (res != -2 && res != -1) {
                updateTask(task, res);
            }
        }

    }

    /* add a new task */
    public long addTask(Task task, final int taskListID) {
        ContentValues values = createContentTasks(task, taskListID, false);
        SQLiteDatabase db = this.getWritableDatabase();

        final long rows = db.insert(TASK_TABLE_NAME, null, values);
        return rows;
    }

    /* update task in db but check before if entry is already newer than given 
     * returns false if given task is older*/
    public long updateTask(Task task, final int ID) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = createContentTasks(task, ID, true);
        String[] whereArgs = {task.getId()};

        final long rows = db.update(TASK_TABLE_NAME, values, TASK_CLM_ID + "=?", whereArgs);
        return rows;
    }

    /* Does set the flags to deleted and hidden, therefore still in the db*/
    public void removeTask(final String id) {
        SQLiteDatabase db = this.getWritableDatabase();
        String[] whereArgs = {id};

        //ContentValues values = new ContentValues();
        //values.put(TASK_CLM_DELETED, TRUE);
        //values.put(TASK_CLM_HIDDEN, TRUE);

        db.delete(TASK_TABLE_NAME, "id=?", whereArgs);
    }

    public String[] getTaskLists() {
        SQLiteDatabase db = this.getReadableDatabase();
        final String[] columns = {TASK_CLM_TITLE};

        Cursor cursor = db.query(TASKLIST_TABLE_NAME, columns, null, null, null, null, null);
        cursor.moveToFirst();

        String[] lists = new String[cursor.getCount()];
        
        final int index = cursor.getColumnIndex(TASK_CLM_TITLE);

        for (int i = 0; i<lists.length; i++) {
            lists[i] = cursor.getString(index);
            cursor.moveToNext();
        }

        return lists;
    }

    /* add a new taskList */
    public long addTaskList(TaskList taskList) {
        ContentValues values = createContentTaskList(taskList, false);
        SQLiteDatabase db = this.getWritableDatabase();

        final long rows = db.insert(TASKLIST_TABLE_NAME, null, values);
        return rows;
    }

    /*Change name or insert new if not already in db*/
    public long updateTaskList(TaskList taskList) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = createContentTaskList(taskList, true);
        String[] whereArgs = {taskList.getId()};

        final long rows = db.update(TASKLIST_TABLE_NAME, values, TASK_CLM_ID + "=?", whereArgs);
        return rows;
    }

    public boolean removeTaskList(TaskList taskList) {
        // do not forget to also remove the tasks
        return false;
    }

    /*If task uploaded successfully it will be marked as modified false or 0*/
    public boolean markAsModified(Task task) {
        return false;
    }

    /*Update status of task, true means status completed, false = needsAction */
    public boolean setTaskCompleted(final String id, final boolean status) {
        SQLiteDatabase db = this.getWritableDatabase();
        final String[] whereArgs = {id};

        ContentValues values = new ContentValues();
        if (status) {
            values.put(TASK_CLM_STATUS, "completed");
        } else {
            values.put(TASK_CLM_STATUS, "needsAction");
        }
        final long rows = db.update(TASK_TABLE_NAME, values, "id=?", whereArgs);
        if (rows == 1)
            return true;
        else
            return false;
    }

    /*Returns a list with all tasks with the modified flag*/
    public List<Task> getModifiedTasks() {
        // do something

        List<Task> tasks = null;
        return tasks;
    }

    /*Returns -2 if task is older and not update shall be done
     *        -1 if this task is new to the db > insert
     *        column number when task is newer than existent in db > update */
    protected int isTaskNewer(final Task task) {
        SQLiteDatabase db = this.getReadableDatabase();
        final String[] columns = {TASK_CLM_ID, TASK_CLM_UPDATED};
        final String[] whereArgs = {task.getId()};

        Cursor cursor = db.query(TASK_TABLE_NAME, columns, TASK_CLM_ID + "=?", whereArgs, 
                                 null, null, null);
        cursor.moveToFirst();
        final int count = cursor.getCount();

        if (count == 0) {
            // no tasks with the ID found
            return -1;
        } else if (count > 1) {
            // should never ever happen, multiple tasks with the same ID
            // TODO add a message a some kind of conflict resolve
            return -1;
        }

        DateTime taskUpdate = task.getUpdated();
        final int index = cursor.getColumnIndex(TASK_CLM_UPDATED);
        DateTime dbUpdated = DateTime.parseRfc3339(cursor.getString(index));

        if (taskUpdate == null || dbUpdated == null) {
            return -1;
        }

        // is taskUpdate is newer return true
        if (taskUpdate.getValue() > dbUpdated.getValue()) {
            return 1;
        } else {
            return -2;
        }
    }

    /* Returns cursor for the list view */
    public Cursor getTasksCursor() {
        SQLiteDatabase db = this.getReadableDatabase();
        final String[] columns = {TASK_CLM_PK, TASK_CLM_ID, TASK_CLM_TITLE,TASK_CLM_NOTES, TASK_CLM_DUE, 
                TASK_CLM_STATUS, TASK_CLM_DELETED};
        return db.query(TASK_TABLE_NAME, columns, null, null, null, null, null);
    }
}